﻿//#undef MySql
//#define MSSql
#define MySql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using MySql.Data.MySqlClient;

namespace ukeasbjWeb
{
    public partial class ContractMan : WebList_SXD  //System.Web.UI.Page
    {
        
        protected void Page_Load(object sender, EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
            Common_SXD.Is_LongIn(Response, Session, Request);

            bool bIs_Cashier = Convert.ToBoolean(Session["Is_Cashier"]);
            bool bIs_G_M = Convert.ToBoolean(Session["Is_G_M"]);
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);

            if (!bIs_Cashier && !bIs_Admin && !bIs_Accountant && !bIs_G_M )
            {
                Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                Response.Write("<br>对不起，您不能使用本功能。" +
                    "<br>请用其它身份登录。");
                Response.Write("</fong></h2>");
                Response.End();

            }


            if (!IsPostBack)
            {	// 初始化页面
                if (bIs_Cashier || bIs_Accountant || bIs_Admin || bIs_G_M)
                    /// Session["Email"].ToString().Trim() == "penny_pan")
                    /// 潘宁，panny_pan 可以看全国的合同管理数据
                    CheckBox_Display_Detailed.Checked = true;
                else
                    CheckBox_Display_Detailed.Checked = false;

                //Init_Office();
                Common_SXD.Init_Office(DDList_Office);
                TextMax_Lines.Text = "88";
                Textbox_LockDay_LastMonth.Text = Common_SXD.get_LockDay_LastMonth(Textbox_LockDay_LastMonth);
                Textbox_LockDay_LastMonth.Enabled = false;

                Common_SXD.Init_Searchkey(DDList_SearchKey, "");

                Init_Term_Search();
                if (CheckBox_Display_Detailed.Checked == true)
                { ///显示详细数据时
                    Common_SXD.Init_Table_Bonus(Table_Bonus, DDList_Office.SelectedItem.Text.Trim());
                    Common_SXD.Init_Table_Bonus_US(Table_Bonus_US, DDList_Office.SelectedItem.Text.Trim());
                    Common_SXD.Init_Table_Bonus_100826(Table_Bonus_100826, DDList_Office.SelectedItem.Text.Trim());
                    
                }
                Common_SXD.Init_Table_Bonus_101217(Table_Bonus_101217, DDList_Office.SelectedItem.Text.Trim());
                Common_SXD.Init_Table_Merit_Pay_140418(Table_Merit_Pay_140418, DDList_Office.SelectedItem.Text.Trim());
                Common_SXD.Init_Table_Counsellor_Bonus_130926(Table_Counsellor_Bonus_130926, DDList_Office.SelectedItem.Text.Trim());
                Common_SXD.Init_Table_Bonus_US_T_110315(Table_Bonus_US_T_110315, DDList_Office.SelectedItem.Text.Trim()); 
                Init_Table_Contract();

            }

            if (bIs_Cashier || bIs_Accountant || bIs_Admin || bIs_G_M)
                /// Session["Email"].ToString().Trim() == "penny_pan")
                /// 潘宁，panny_pan 可以看全国的合同管理数据
            { /// 设定各地办公室list控件
                DDList_Office.Enabled = true;
                DDList_Office.Visible = true;
            }
            else
            {
                DDList_Office.Enabled = false;
                //DDList_Office.Visible = false;
                string temp = Session["Office"].ToString().Trim();
                Common_SXD.SetSelectedIndex(temp, DDList_Office);

            }

        }

        /// <summary>
        /// 显示合同列表2的：
        /// 输入参数： 
        /// sSearchKey,查询条件； sOffice,地区； 日期起点,sStart_Date; 日期终点,sEnd_Date; 
        /// sSearchValue,查询值； 
        /// </summary>
        /// <returns></returns>
        private void Disp_Search_Contract_2(string sSearchKey, string sOffice, string sStart_Date, string sEnd_Date, string sSearchValue)
        {
            return;
        }

        /// <summary>
        /// 显示合同列表3的：记录数
        /// 输入参数： 
        /// sSearchKey,查询条件； sOffice,地区； 日期起点,sStart_Date; 日期终点,sEnd_Date; 
        /// sSearchValue,查询值； 
        /// </summary>
        /// <returns></returns>
        private void Disp_Search_Contract_3(string sSearchKey, string sOffice, string sStart_Date, string sEnd_Date, string sSearchValue)
        {
#if MSSql
            //记录个数
            string str = Common_SXD.CHAR_MSSQL_PLUS1("select count(Series_No) from Contract",
                Common_SXD.Filter_Search_Contract_3(sSearchKey, sOffice, TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue), Response);
#elif MySql
            //记录个数
            string str = Common_SXD.CHAR_MySQL_PLUS1("select count(Series_No) from Contract",
                Common_SXD.Filter_Search_Contract_3(sSearchKey, sOffice, TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue), Response);
#endif
            Label_NotDeduct.Text = sSearchKey + "不扣奖: " + Convert.ToString(Convert.ToUInt64(str) - 1);

            return;
        }


        /// <summary>
        ///  初始化合同数据表
        /// </summary>
        private void Init_Table_Contract()
        {
            //  在此处放置用户代码以初始化页面
            //////////////////////////////////////////////////////
            string sBClassID = DDList_Office.SelectedItem.Text;
            string sSearchKey = DDList_SearchKey.SelectedItem.Text; //查询条件
            string sSearchValue = TextBox_SearchValue.Text.Trim();
            bool bIs_Cashier = Convert.ToBoolean(Session["Is_Cashier"]);
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);

            Session.Timeout = 118;
            Date_Msg.Text = "收(退)费日期:";

            //清除表中的内容
            Label_Total.Text = "";
            Label_Total_First_Payment.Text = "";
            Label_Total_Second_Payment.Text = "";
            Label_Total_Termination_Refund.Text = "";
            Table_Contract.Rows.Clear();
            //生成 sql 命令串
            string sSql  = Common_SXD.Search_Contract(sSearchKey, sBClassID, TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue, Session, Response, CheckBox_Display_Detailed,
                TextMax_Lines.Text, Date_Msg, CheckBox_SecondOrder_Destinations_OS);
#if MSSql
            ///打开数据库，获取sqlReader变量
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错：", Response);
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

#elif MySql
            sSql += Limit_MySql(TextMax_Lines.Text);

            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = MysqlReader.FieldCount;

#endif
            Common_SXD.Disp_Search_Contract(sSearchKey, sBClassID, TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue, Session,
                Label_Total, Label_Total_First_Payment,
                Label_Total_Second_Payment, Label_Total_Termination_Refund, Response, Date_Msg);

            // 读入第一个查询结果集， 即 Contract 中的数据
            ///int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
#elif MySql
                    string temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
#endif
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }
                if (sSearchKey == "未确认(一期款)" || sSearchKey == "未确认(二期款)" || sSearchKey == "未确认合同原件")
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl("确认收款"));
                    row.Cells.Add(cell);
                }
                else if (sSearchKey == "待支付的零中介退款" || sSearchKey == "待支付的客户退款")
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl("确认退款"));
                    row.Cells.Add(cell);

                }
                else if (sSearchKey == "未开票" )
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl("确认收入（开票）"));
                    row.Cells.Add(cell);

                }
                else if (sSearchKey == "已确认(一期款)" || sSearchKey == "一期款开收据日期"
                    || sSearchKey == "二期款开收据日期")
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl("二期款/执行情况"));
                    row.Cells.Add(cell);

                }
                Table_Contract.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            int j = 0;
#if MSSql
            while (sqlReader.Read())
#elif MySql
            while (MysqlReader.Read())
#endif 
            {
                HtmlTableRow row = new HtmlTableRow();
#if MSSql
                String sNo_Series = sqlReader.GetString(0);
#elif MySql
                String sNo_Series = MysqlReader.GetString(0);
#endif
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                // Iterate through the cells of a row.
                {
                    HtmlTableCell cell = new HtmlTableCell();

                    cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=ReadOnly:" +
                        sNo_Series.Trim() +
                        "',900,620);\">只读" +
                        sNo_Series.Trim() +
                        "</a>"));
                    row.Cells.Add(cell);

                }

                for (int i = 1; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
#elif MySql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
#endif
                    row.Cells.Add(cell);
                }


                if (sSearchKey == "未确认(一期款)")
                {
                    string sNotes;
                    if (CheckBox_Display_Detailed.Checked == true)
                    { ///显示详细数据时
#if MSSql
                        sNotes = Common_SXD.TimeToYMD(sqlReader.GetValue(21).ToString());
#elif MySql
                        sNotes = Common_SXD.TimeToYMD(MysqlReader.GetValue(21).ToString());
#endif 
                    }
                    else
                    {
#if MSSql
                        sNotes = Common_SXD.TimeToYMD(sqlReader.GetValue(16).ToString());
#elif MySql
                        sNotes = Common_SXD.TimeToYMD(MysqlReader.GetValue(16).ToString());
#endif 

                    }
                    int intTemp = sNotes.IndexOfAny(new Char[] { '上', '海' });

                    if (intTemp != -1)
                    {
                        sNotes = Common_SXD.TimeToYMD(sNotes);
                    }
#if MSSql
                    String sOffice = sqlReader.GetString(1).ToString();
#elif MySql
                    String sOffice = MysqlReader.GetString(2).ToString();
#endif
                    Regex BeijingZhonghang = new Regex("(北京中行)");
                    Regex BeijingZhongguoyinhang = new Regex("(北京中国银行)");

                    if (Session["Email"].ToString().Trim().ToLower() == "joanna_bai")
                    {//////特殊权限，joanna_bai 能确认 BD 办公室一期款。
                        HtmlTableCell cell = new HtmlTableCell();
                        if ( sOffice == "BD" )
                        {
                            cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractFirstPay:" +
                                sNo_Series.Trim() +
                                "',900,620);\">确认收款</a>"));

                            row.Cells.Add(cell);
                        }

                    }
                    else if (bIs_Cashier)
                    {//////只有总部出纳能修改
                        HtmlTableCell cell = new HtmlTableCell();
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractFirstPay:" +
                                    sNo_Series.Trim() +
                                    "',900,620);\">确认收款</a>"));

                        row.Cells.Add(cell);

/*
                        if ( Session["Office"].ToString() == "上海")
                        {// 上海修改上海的到账数据
//                            HtmlTableCell cell = new HtmlTableCell();
//
//                            cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractFirstPay:" +
//                                sNo_Series.Trim() +
//                                "',800,520);\">确认收款</a>"));
//
//                            row.Cells.Add(cell);
                            //////上海出纳特殊权限，能修改：北京银行和上海银行的合同数据。
                            HtmlTableCell cell = new HtmlTableCell();
                            
                            if ( (sOffice != "北京") &&  (sNotes.Length > 0) &&
                                 !BeijingZhonghang.IsMatch(sNotes) && !BeijingZhongguoyinhang.IsMatch(sNotes)
                                )
                            {//////上海出纳不能确认的数据：北京的，外地的一期款汇款行（二期款汇款行）为：北京中行，北京中国银行的数据。
                               cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractFirstPay:" +
                                    sNo_Series.Trim() +
                                    "',900,620);\">确认收款</a>"));

                                row.Cells.Add(cell);
                           
                        }
                        else if (intTemp == -1 && Session["Office"].ToString() == "北京")
                             {// 北京修改上海以外的到账数据
                                HtmlTableCell cell = new HtmlTableCell();

                                cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractFirstPay:" +
                                    sNo_Series.Trim() +
                                    "',900,620);\">确认收款</a>"));

                                row.Cells.Add(cell);
                             }
*/
                    }

                }

                if (sSearchKey == "未确认合同原件")
                { ///只有 joanna bai 能够添加原件返回日期
                    HtmlTableCell cell = new HtmlTableCell();
                    if ( Session["Email"].ToString().Trim().ToLower() == "joanna_bai" )
                    {

                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractFirstPay:" +
                            sNo_Series.Trim() +
                            "',900,620);\">确认收款</a>"));

                        row.Cells.Add(cell);
                    }


                }

                if (sSearchKey == "未确认(二期款)")//////
                {
#if MSSql
                    string sRemitter = Common_SXD.TimeToYMD(sqlReader.GetValue(7).ToString()); // 汇款人 汇款行 备注
#elif MySql
                    string sRemitter = Common_SXD.TimeToYMD(MysqlReader.GetValue(7).ToString()); // 汇款人 汇款行 备注
#endif
                    int intTemp = sRemitter.IndexOfAny(new Char[] { '上', '海' });

                    if (intTemp != -1)
                    {
                        sRemitter = Common_SXD.TimeToYMD(sRemitter);
                    }
#if MSSql
                    String sOffice = sqlReader.GetString(1).ToString();
#elif MySql
                    String sOffice = MysqlReader.GetString(1).ToString();
#endif
                    Regex BeijingZhonghang = new Regex("(北京中行)");
                    Regex BeijingZhongguoyinhang = new Regex("(北京中国银行)");


                    if (Session["Email"].ToString() == "joanna_bai0")
                    {//////特殊权限，小陈能修改：北京银行和上海银行的合同数据。
                        HtmlTableCell cell = new HtmlTableCell();
                        if ((sOffice != "北京") && (sRemitter.Length > 0) &&
                            !BeijingZhonghang.IsMatch(sRemitter) && !BeijingZhongguoyinhang.IsMatch(sRemitter)
                            )
                        {//////特殊权限，小陈不能确认的数据：北京的，外地的一期款汇款行（二期款汇款行）为：北京中行，北京中国银行的数据。

                            cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractSecondPay:" +
                                sNo_Series.Trim() +
                                "',900,620);\">确认收款</a>"));

                            row.Cells.Add(cell);
                        }

                    }
                    else if (bIs_Cashier)
                    {//////只有总部出纳能修改
                        HtmlTableCell cell = new HtmlTableCell();
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractFirstPay:" +
                                    sNo_Series.Trim() +
                                    "',900,620);\">确认收款</a>"));

                        row.Cells.Add(cell);
/*
                        if ( Session["Office"].ToString() == "上海")
                        {// 上海修改上海的到账数据
//                            HtmlTableCell cell = new HtmlTableCell();
//
//                            cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractSecondPay:" +
//                                sNo_Series.Trim() +
//                                "',880,560);\">确认收款</a>"));
//                            row.Cells.Add(cell);
                            //////上海出纳特殊权限，能修改：北京银行和上海银行的合同数据。
                            HtmlTableCell cell = new HtmlTableCell();
                            if ((sOffice != "北京") && (sRemitter.Length > 0) &&
                                !BeijingZhonghang.IsMatch(sRemitter) && !BeijingZhongguoyinhang.IsMatch(sRemitter)
                                )
                            {//////上海出纳特殊权限不能确认的数据：北京的，外地的一期款汇款行（二期款汇款行）为：北京中行，北京中国银行的数据。

                                cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractSecondPay:" +
                                    sNo_Series.Trim() +
                                    "',900,620);\">确认收款</a>"));

                                row.Cells.Add(cell);
                            }

                        }
                        else
                            if (intTemp == -1 && Session["Office"].ToString() == "北京")
                            {// 北京修改上海以外的到账数据
                                HtmlTableCell cell = new HtmlTableCell();

                                cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckContractSecondPay:" +
                                    sNo_Series.Trim() +
                                    "',900,620);\">确认收款</a>"));
                                row.Cells.Add(cell);
                            }

 */ 
                    }

                }

                if (sSearchKey == "待支付的零中介退款" || sSearchKey == "待支付的客户退款")//////
                {

                    if (Session["Email"].ToString() == "joanna_bai0" || bIs_Cashier)
                    {//////只有总部出纳能确认退款
                        HtmlTableCell cell = new HtmlTableCell();

                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=CheckFinishedTerminationRefund:" +
                            sNo_Series.Trim() +
                            "',900,620);\">确认退款</a>"));
                        row.Cells.Add(cell);
                        
                    }

                }

                if (sSearchKey == "未开票" )
                {
                    if (bIs_Accountant)
                    {//只有总部会计能修改
                        HtmlTableCell cell = new HtmlTableCell();

                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=EditContractSecondPay:" +
                           sNo_Series.Trim() +
                           "',900,620);\">确认收入</a>"));
                        row.Cells.Add(cell);

                    }

                }

                if (sSearchKey == "已确认(一期款)" || sSearchKey == "一期款开收据日期"
                    || sSearchKey == "二期款开收据日期" )
                {
                    if (bIs_O_M || bIs_Accountant || bIs_Cashier)
                    {
                        HtmlTableCell cell = new HtmlTableCell();
                        //				if(bIs_Collector)
                        //				{//////只有营运经理、总部会计和总部出纳能修改...
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=EditContractSecondPay:" +
                            sNo_Series.Trim() +
                            "',900,620);\">二期款/执行情况</a>"));
                        row.Cells.Add(cell);
                        //				}
                    }
                    
                }
              
                Table_Contract.Rows.Add(row);
                j++;
            }
#if MSSql
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接
            
#endif
        }

        /// <summary>
        ///  初始化合同数据表_2。零中介退款。
        /// </summary>
        private void Init_Table_Contract_2()
        {
            //  在此处放置用户代码以初始化页面
            //////////////////////////////////////////////////////
            string sBClassID = DDList_Office.SelectedItem.Text;
            string sSearchKey = DDList_SearchKey.SelectedItem.Text; //查询条件
            string sSearchValue = TextBox_SearchValue.Text.Trim();
            bool bIs_Cashier = Convert.ToBoolean(Session["Is_Cashier"]);

            //清除表中的内容
            Label_Deduct.Text = "";
            Table_Contract_2.Rows.Clear();

            if (sSearchKey != "零中介退款")
            {
                return;
            }
            /*
            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象
            */
            //生成 sql 命令串
            string sSql = Common_SXD.Search_Contract_2(sSearchKey, sBClassID, TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue, TextMax_Lines.Text);
#if MSSql
            ///打开数据库，获取sqlReader变量
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错：", Response);

            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //记录个数
            string str = Common_SXD.CHAR_MSSQL_PLUS1("select count(Series_No) from Contract",
                Common_SXD.Filter_Search_Contract_2(sSearchKey, sBClassID, TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue), Response);
#elif MySql
            sSql += Limit_MySql(TextMax_Lines.Text);

            ///打开数据库，
            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = MysqlReader.FieldCount;

            //记录个数
            string str = Common_SXD.CHAR_MySQL_PLUS1("select count(Series_No) from Contract",
                Common_SXD.Filter_Search_Contract_2(sSearchKey, sBClassID, TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue), Response);
#endif

            Label_Deduct.Text = "扣奖: " + Convert.ToString(Convert.ToUInt64(str) - 1);

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
#elif MySql
                    string temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
#endif
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Contract_2.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            int j = 0;
#if MSSql
            while (sqlReader.Read())
#elif MySql
            while (MysqlReader.Read())
#endif
            {
                HtmlTableRow row = new HtmlTableRow();
#if MSSql
                String sNo_Series = sqlReader.GetString(0);
#elif MySql
                String sNo_Series = MysqlReader.GetString(0);
#endif
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                // Iterate through the cells of a row.
                {
                    HtmlTableCell cell = new HtmlTableCell();

                    cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=ReadOnly:" +
                        sNo_Series.Trim() +
                        "',900,620);\">" +
                        sNo_Series.Trim() +
                        "</a>"));
                    row.Cells.Add(cell);

                }

                for (int i = 1; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
#elif MySql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
#endif
                    row.Cells.Add(cell);
                }



                Table_Contract_2.Rows.Add(row);
                j++;
            }
#if MSSql
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接
            
#endif
        }

        /// <summary>
        ///  初始化合同数据表_3。 零中介退款，扣奖情况
        /// </summary>
        private void Init_Table_Contract_3()
        {
            //  在此处放置用户代码以初始化页面
            //////////////////////////////////////////////////////
            string sBClassID = DDList_Office.SelectedItem.Text;
            string sSearchKey = DDList_SearchKey.SelectedItem.Text; //查询条件
            string sSearchValue = TextBox_SearchValue.Text.Trim();
            bool bIs_Cashier = Convert.ToBoolean(Session["Is_Cashier"]);

            //清除表中的内容
            Label_NotDeduct.Text = "";
            Table_Contract_3.Rows.Clear();

            if (sSearchKey != "零中介退款")
            {
                return;
            }
            /*
            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象
            */
            //生成 sql 命令串
            string sSql = Common_SXD.Search_Contract_3(sSearchKey, sBClassID, TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue, TextMax_Lines.Text);
#if MSSql
            ///打开数据库，获取sqlReader变量
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错：", Response);

            Disp_Search_Contract_3(sSearchKey, sBClassID, TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue);

            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;
#elif MySql
            sSql += Limit_MySql(TextMax_Lines.Text);

            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);

            Disp_Search_Contract_3(sSearchKey, sBClassID, TextBox_Date_start_collector.Text,
                TextBox_Date_end_collector.Text, sSearchValue);

            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = MysqlReader.FieldCount;
#endif


            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
#elif MySql
                    string temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
#endif
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Contract_3.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            int j = 0;
#if MSSql
            while (sqlReader.Read())
#elif MySql
            while (MysqlReader.Read())
#endif
            {
                HtmlTableRow row = new HtmlTableRow();
#if MSSql
                String sNo_Series = sqlReader.GetString(0);
#elif MySql
                String sNo_Series = MysqlReader.GetString(0);
#endif
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                // Iterate through the cells of a row.
                {
                    HtmlTableCell cell = new HtmlTableCell();

                    cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./ContractEdit.aspx?Series_No=ReadOnly:" +
                        sNo_Series.Trim() +
                        "',900,620);\">" +
                        sNo_Series.Trim() +
                        "</a>"));
                    row.Cells.Add(cell);

                }

                for (int i = 1; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
#elif MySql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
#endif
                    row.Cells.Add(cell);
                }

                Table_Contract_3.Rows.Add(row);
                j++;
            }
#if MSSql
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接
            
#endif
        }

        private void Init_Term_Search()
        {
            TextBox_Date_start_collector.Text = Common_SXD.startDate_CurrentMonth();
            TextBox_Date_end_collector.Text = Common_SXD.TimeToYMD(DateTime.Now.ToString());


        }
        protected void Bt_Search_Click(object sender, EventArgs e)
        {
            // 点击查询按钮后，重新绑定数据
            if (CheckBox_Display_Detailed.Checked == true)
            { ///显示详细数据时
                Common_SXD.Init_Table_Bonus(Table_Bonus, DDList_Office.SelectedItem.Text.Trim());
                Common_SXD.Init_Table_Bonus_US(Table_Bonus_US, DDList_Office.SelectedItem.Text.Trim());
                Common_SXD.Init_Table_Bonus_100826(Table_Bonus_100826, DDList_Office.SelectedItem.Text.Trim());
                
            }
            Common_SXD.Init_Table_Bonus_101217(Table_Bonus_101217, DDList_Office.SelectedItem.Text.Trim());
            Common_SXD.Init_Table_Merit_Pay_140418(Table_Merit_Pay_140418, DDList_Office.SelectedItem.Text.Trim());
            Common_SXD.Init_Table_Counsellor_Bonus_130926(Table_Counsellor_Bonus_130926, DDList_Office.SelectedItem.Text.Trim());
            Common_SXD.Init_Table_Bonus_US_T_110315(Table_Bonus_US_T_110315, DDList_Office.SelectedItem.Text.Trim()); 
            Init_Table_Contract();
            Init_Table_Contract_2();
            Init_Table_Contract_3();
		
        }

    }
}
